<?php

namespace App\Console\Commands;

use App;
use App\Facades\ExcelService;
use DB;
use Mail;
use Excel;
use Exception;
use Illuminate\Console\Command;
use Storage;

class ExcelImport extends Command{

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'excel:import';

    private $_file;
    private $_sheetName;

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'import excel data to database from storage\app\uploads\\';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct(){
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle(){


        try{
            
            DB::transaction(function(){

                //获取目录下的所有文件
                $files = Storage::files('uploads');
                $bar = $this->output->createProgressBar(count($files));
                
                //循环文件
                foreach($files as $file){
                    $this->_file = $file;
                    //获取单个文件全部工作表名
                    $workSheetNames = Excel::load('storage/app/'.$file)->excel->getSheetNames();
                    
                    //循环取单个工作表数据
                    foreach($workSheetNames as $workSheetName){
                        $this->_sheetName = $workSheetName;
                        //分块获取数据，每块300条数据
                        Excel::filter('chunk')->selectSheets($workSheetName)->load('storage/app/'.$file)->chunk(300, function($results) use ($workSheetName) {
                            
                            foreach($results as $row){
                                $excelService = ExcelService::importDataToDatabase($workSheetName, $row);
                            }
                            
                        }, false);
                    }
                    //清空所选工作表，重设为默认值
                    App::make('excel.reader')->setSelectedSheets([]);
                    $bar->advance();
                }
                $bar->finish();
            });
        }catch(Exception $e){
            //出现异常，组织提示信息并提醒管理员
            $data = [
                'file' => $this->_file,
                'sheetName' => $this->_sheetName,
                'errMessage' => $e->getMessage(),
            ];
            //发送提醒邮件
            Mail::send('mail.notify.excel-import-fail', $data, function ($message) {
                            $message->from(env('MAIL_FROM_ADDRESS'));
                            $message->subject('导入excel数据到数据库出错');
                            $message->to(env('MAIL_TARET_ADDRESS'));
                        });
        }
    }
}
